package cn.lijian.goods.category.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.commons.CommonUtils;
import cn.itcast.jdbc.TxQueryRunner;
import cn.lijian.goods.category.domain.Category;

public class CategoryDao {
	
	private QueryRunner qr=new TxQueryRunner();
	/**
	 * 查询指定父分类下子分类的个数
	 * @param pid
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public int findChildrenCountByParent(String pid) throws SQLException{
		String sql="select count(*) from t_category where pid=?";
		Number num=(Number) qr.query(sql, new ScalarHandler(),pid);
		return num==null?0:num.intValue();
	}
	/**
	 * 删除分类
	 * @param cid
	 * @author lijian
	 * @throws SQLException 
	 */
	public void delete(String cid) throws SQLException{
		String sql="delete from t_category where cid=?";
		qr.update(sql,cid);
	}
	
	/**
	 * 修改分类
	 * 	既可以修改一级分类，也可以修改二级分类
	 * @param category
	 * @author lijian
	 * @throws SQLException 
	 */
	public void edit(Category category) throws SQLException{
		String sql="update t_category set cname=?,pid=?,`desc`=? where cid=?";
		String pid=null;
		if(category.getParent()!=null){
			pid=category.getParent().getCid();
		}
		Object[]params={category.getCname(),pid,category.getDesc(),category.getCid()};
		qr.update(sql,params);
	}
	
	/**
	 * 加载 
	 * 	既可以加载一级分类，又可以加载二级分类
	 * @param cid
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public Category load(String cid) throws SQLException{
		String sql="select * from t_category where cid=?";
		return toCategory(qr.query(sql, new MapHandler(),cid));
	}
	
	/**
	 * 获取所有父分类，不带子分类
	 * @return
	 * @throws SQLException
	 * @author lijian
	 */
	public List<Category>findParents() throws SQLException{
		
		String sql="select * from t_category where pid is null order by orderBy";
		List<Map<String,Object>>mapList=qr.query(sql, new MapListHandler());
		return toCategoryList(mapList);
	}
	
	/**
	 * 添加分类
	 * @param category
	 * @author lijian
	 * @throws SQLException 
	 */
	public void add(Category category) throws SQLException{
		String sql="insert into t_category(cid,cname,pid,`desc`) values(?,?,?,?)";;
		String pid=null;
		if(category.getParent()!=null){//二级分类
			pid=category.getParent().getCid();
		}
		Object[]params={category.getCid(),category.getCname(),pid,category.getDesc()};
		qr.update(sql,params);
	}
	
	/**
	 * 把一个 map 中的数据映射到 Category 中
	 * @param map
	 * @return
	 * @author lijian
	 */
	private Category toCategory(Map<String,Object>map){
		Category category=CommonUtils.toBean(map, Category.class);
		String pid=(String)map.get("pid");
		if(pid!=null){
			Category parent=new Category();
			parent.setCid(pid);
			category.setParent(parent);
		}
		
		return category;
	}
	/**
	 * 把多个 map 映射成多个 Category 
	 * @param mapList
	 * @return
	 * @author lijian
	 */
	private List<Category> toCategoryList(List<Map<String,Object>>mapList){
		List<Category>categoryList=new ArrayList<Category>();
		for(Map<String,Object>map:mapList){
			Category c=toCategory(map);
			categoryList.add(c);
		}
		return categoryList;
	}
	
	/**
	 * 返回所有分类
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public List<Category>findAll() throws SQLException{
		
		String sql="select * from t_category where pid is null order by orderBy";
		List<Map<String,Object>>mapList=qr.query(sql, new MapListHandler());
		List<Category>parents=toCategoryList(mapList);
		
		//循环遍历一级分类，为每个一级分类加载它的二级分类
		
		for(Category parent:parents){
			//查询当前父分类的所有子分类
			List<Category>children=findByParent(parent.getCid());
			parent.setChildren(children);
		}
		
		return parents;
	}
	/**
	 * 通过 父分类查询子分类
	 * @param pid
	 * @return
	 * @author lijian
	 * @throws SQLException 
	 */
	public List<Category>findByParent(String pid) throws SQLException{
		String sql="select * from t_category where pid=?";
		List<Map<String,Object>>children=qr.query(sql, new MapListHandler(),pid);
		return toCategoryList(children);
	}

}
